Attribute VB_Name = "SROutputFile"
'**WRITTEN BY JALISA GREENE'
'**LAST MODIFIED BY JALISA GREENE
'**LAST MODIFIED 6/10/2014'

Dim rowNum As Integer
Dim LocID As Integer
Dim Col As Long
Dim total As Double

Sub GenerateSReport()
    msg = MsgBox("This file will take approximately 1 full minute to generate. During this time you will not be able to access anyting in Microsoft Excel. Please be patient while your Savings Report is being generated...Click OK to begin.", vbOKCancel)
    If msg = 1 Then
         Application.ScreenUpdating = False
         Worksheets("Solid Monthly").Activate
         '**CLEAR ALL PREVIOUS DATA**'
         ActiveSheet.Rows("14:1000").ClearContents
         ActiveSheet.Rows("14:1000").ClearFormats
         '**REMOVE PREVIOUS BACKGROUND COLOR**'
         ActiveSheet.Rows("14:1000").Interior.Color = RGB(255, 255, 255)
         ActiveSheet.Range("A14").Select
         
         '**COPY LOCATION ID VALUES**'
         Dim loc As Range
         For Each loc In Range("SRLoc")
             If loc <> "" And loc.Offset(0, 3).Value <> 0 Then
                  ActiveCell.Value = loc.Value
                  ActiveCell.Offset(1).Select
             End If
         Next
         
         
         '**INSERT CLIENT NAME, DATE, SOURCE, AMOUNT
         Call InsertHeader
         Call InsertAddr
         Call TestVendors
         Call InsertAccountTotals
         
         '**CLEAR THE LAST ROW OF THE EOF MARKER**'
         Dim lastRow As Integer
        lastRow = Worksheets("Solid Monthly").Range("A1500").End(xlUp).Row
        Range("A" & lastRow).ClearContents
        
        '**FORMATTING, ARIAL FONT 14PT, ALIGNMENT'
        lastRow = lastRow + 3
        With Range("A13", "S" & lastRow)
             .Font.Name = "Arial"
             .Font.Size = 14
             .VerticalAlignment = xlCenter
        End With
         Range("D:E").EntireColumn.HorizontalAlignment = xlCenter
         Application.ScreenUpdating = True
    End If
End Sub

Private Sub InsertHeader()
'**INSERTS CLIENT NAME, DATE, SOURCE, AMOUNT AT THE TOP**'
    Range("C3").Value = Worksheets("SR Data").Range("B1").Value
    Range("C4").Value = Format(Worksheets("SR Data").Range("B2").Value, "mm/dd/yyyy")
    Range("C7").Value = Worksheets("SR Data").Range("B5").Value
    Range("C8").Value = Worksheets("SR Data").Range("B6").Value
    Range("C9").Value = Format(Worksheets("SR Data").Range("B7").Value, "Currency")
End Sub
Private Sub InsertAddr()

'**FOR EACH LOCATION, INSERT IN ADDRESS, OTHER**'
Dim loc As Range
    For Each loc In Range("SRLocationsOUT")
        loc.Select
        If TypeName(loc.Value) = "Double" Then
            rowNum = WorksheetFunction.Match(loc, Worksheets("SR Data").Range("SRLoc"), 0) + 8
            ActiveCell.Offset(0, 1).Value = Worksheets("SR Data").Range("B" & rowNum)
            ActiveCell.Offset(0, 2).Value = Worksheets("SR Data").Range("C" & rowNum)
        End If
    Next
End Sub

Private Sub TestVendors()
'**THIS SUB WILL TEST TO SEE IF WE HAVE EACH VENDOR (UP TO 6), IF SO WE WILL ENTER IN THE RELATED CHARGES**'

    Dim LastVendor As String
    Dim loc As Range
    Dim VendorMarker As Range
    
    Set VendorMarker = ActiveCell
    For Each loc In Range("SRLocationsOUT")
    '**IF WE ARE ON A LOCATION ID, INSERT INFORMATION**'
    If TypeName(loc.Value) = "Double" Then
        loc.Select
        '**MATCH THE LOCATION TO ITS CORRESPONDING ROW IN THE SR DATA TAB**'
        rowNum = WorksheetFunction.Match(loc, Worksheets("SR Data").Range("SRLoc"), 0) + 8
        loc.Offset(0, 3).Select
        ActiveCell.Offset(1).EntireRow.Insert
        '**TEST VENDOR 1
        If Worksheets("SR Data").Range("F" & rowNum).Value <> "" Then
            '**INSERT VALUES**'
            ActiveCell.Value = Worksheets("SR Data").Range("F" & rowNum).Value
            ActiveCell.Offset(0, 1).Value = Worksheets("SR Data").Range("G" & rowNum).Value
            LastVendor = Worksheets("SR Data").Range("F" & rowNum).Value
            '**VENDOR MARKER IS USED TO HAVE A PLACEHOLDER FOR WHERE OUR CURRENT VENDOR'S INFORMATION IS**'
            '**THIS BECOMES IMPORTANT WHEN WE HAVE MORE THATN ONE VENDOR FOR THE SAME LOCATION**'
            Set VendorMarker = ActiveCell
            Call V1Charges(rowNum)
        End If
        
        '**TEST VENDOR 2**'
        VendorMarker.Select
        If Worksheets("SR Data").Range("U" & rowNum).Value <> "" Then
            If Worksheets("SR Data").Range("U" & rowNum).Value <> LastVendor Then
            '**THIS IS A DIFFERENT VENDOR**
            '**first find empty row
                While ActiveCell.Value <> "" Or ActiveCell.Offset(0, 3).Value <> ""
                    ActiveCell.Offset(1).Select
                Wend
                ActiveCell.Offset(1).EntireRow.Insert
                ActiveCell.Offset(1).EntireRow.Insert
                ActiveCell.Offset(1).Select
                '**insert vendor and account num
                ActiveCell.Value = Worksheets("SR Data").Range("U" & rowNum).Value
                ActiveCell.Offset(0, 1).Value = Worksheets("SR Data").Range("V" & rowNum).Value
                LastVendor = Worksheets("SR Data").Range("U" & rowNum).Value
                Set VendorMarker = ActiveCell
                Call V2Charges(rowNum)
            Else:
            '**THIS IS THE SAME VENDOR AS #1, INSERT CHARGES**'
                Call V2Charges(rowNum)
            End If
        Else:
        '**THIS IS THE SAME VENDOR AS #1, INSERT CHARGES**'
            Call V2Charges(rowNum)
        End If
        
        '**TEST VENDOR 3**'
        VendorMarker.Select
        If Worksheets("SR Data").Range("AH" & rowNum).Value <> "" Then
            If Worksheets("SR Data").Range("AH" & rowNum).Value <> LastVendor Then
            '**DIFFERENT VENDOR**'
            '**first find an empty row
                While ActiveCell.Value <> "" Or ActiveCell.Offset(0, 3).Value <> ""
                    ActiveCell.Offset(1).Select
                Wend
                ActiveCell.Offset(1).EntireRow.Insert
                ActiveCell.Offset(1).EntireRow.Insert
                ActiveCell.Offset(1).Select
                '**insert vendor and account number
                ActiveCell.Value = Worksheets("SR Data").Range("AH" & rowNum).Value
                ActiveCell.Offset(0, 1).Value = Worksheets("SR Data").Range("AI" & rowNum).Value
                LastVendor = Worksheets("SR Data").Range("AH" & rowNum).Value
                Set VendorMarker = ActiveCell
                Call V3Charges(rowNum)
            Else:
            '**SAME VENDOR AS #2, INSERT CHARGES**
                Call V3Charges(rowNum)
            End If
        Else:
            '**SAME VENDOR AS #2, INSERT CHARGES**'
            Call V3Charges(rowNum)
        End If
        
        '**TEST VENDOR 4**'
        VendorMarker.Select
        If Worksheets("SR Data").Range("AU" & rowNum).Value <> "" Then
            If Worksheets("SR Data").Range("AU" & rowNum).Value <> LastVendor Then
            '**DIFFERENT VENDOR**
            '**first find an empty row
                While ActiveCell.Value <> "" Or ActiveCell.Offset(0, 3).Value <> ""
                    ActiveCell.Offset(1).Select
                Wend
                ActiveCell.Offset(1).EntireRow.Insert
                ActiveCell.Offset(1).EntireRow.Insert
                ActiveCell.Offset(1).Select
                '**insert vendor and account number
                ActiveCell.Value = Worksheets("SR Data").Range("AU" & rowNum).Value
                ActiveCell.Offset(0, 1).Value = Worksheets("SR Data").Range("AV" & rowNum).Value
                LastVendor = Worksheets("SR Data").Range("AU" & rowNum).Value
                Set VendorMarker = ActiveCell
                Call V4Charges(rowNum)
            Else:
            '**SAME VENDOR AS #3, INSERT CHARGES**'
                Call V4Charges(rowNum)
            End If
        Else:
            '**SAME VENDOR AS #3, INSERT CHARGES**'
            Call V4Charges(rowNum)
        End If
        
        
        '**TEST VENDOR 5
        VendorMarker.Select
        If Worksheets("SR Data").Range("BH" & rowNum).Value <> "" Then
            If Worksheets("SR Data").Range("BH" & rowNum).Value <> LastVendor Then
            '**DIFFERENT VENDOR**'
            '**first find an empty row
                While ActiveCell.Value <> "" Or ActiveCell.Offset(0, 3).Value <> ""
                    ActiveCell.Offset(1).Select
                Wend
                ActiveCell.Offset(1).EntireRow.Insert
                ActiveCell.Offset(1).EntireRow.Insert
                ActiveCell.Offset(1).Select
                '**insert vendor and account number
                ActiveCell.Value = Worksheets("SR Data").Range("BH" & rowNum).Value
                ActiveCell.Offset(0, 1).Value = Worksheets("SR Data").Range("BI" & rowNum).Value
                LastVendor = Worksheets("SR Data").Range("BH" & rowNum).Value
                Set VendorMarker = ActiveCell
                Call V5Charges(rowNum)
            Else:
            '**SAME VENDOR AS #4, INSERT CHARGES**'
                Call V5Charges(rowNum)
            End If
        Else:
            '**SAME VENDOR AS #4, INSERT CHARGES**'
            Call V5Charges(rowNum)
        End If
        
        '**TEST VENDOR 6
        VendorMarker.Select
        If Worksheets("SR Data").Range("BU" & rowNum).Value <> "" Then
            If Worksheets("SR Data").Range("BU" & rowNum).Value <> LastVendor Then
            '**DIFFERENT VENDOR**'
            '**first find an empty row
                While ActiveCell.Value <> "" Or ActiveCell.Offset(0, 3).Value <> ""
                    ActiveCell.Offset(1).Select
                Wend
                ActiveCell.Offset(1).EntireRow.Insert
                ActiveCell.Offset(1).EntireRow.Insert
                ActiveCell.Offset(1).Select
                '**insert vendor and account number
                ActiveCell.Value = Worksheets("SR Data").Range("BU" & rowNum).Value
                ActiveCell.Offset(0, 1).Value = Worksheets("SR Data").Range("BV" & rowNum).Value
                LastVendor = Worksheets("SR Data").Range("BU" & rowNum).Value
                Set VendorMarker = ActiveCell
                Call V6Charges(rowNum)
            Else:
            '**SAME VENDOR AS #5, INSERT CHARGES**'
                Call V6Charges(rowNum)
            End If
        Else:
            '**SAME VENDOR AS #5, INSERT CHARGES**'
            Call V6Charges(rowNum)
        End If
        
    End If
    Next
End Sub

Private Sub InsertAccountTotals()
'**THIS SUB WILL PROVIDE ACCOUNT TOTALS FOR EACH LOCATION, AND END OF FILE TOTALS**'

Dim loc As Range
Dim lastRow As Integer
Dim Ender As Integer


    lastRow = Worksheets("Solid Monthly").Range("A1500").End(xlUp).Row
    Ender = Worksheets("Solid Monthly").Range("G1500").End(xlUp).Row + 2
    total = 0
    Col = RGB(255, 255, 255)
    
    '**PUT A 0 AT THE END FOR EOF MARKER**'
    Range("A" & Ender).Value = 0
    For Each loc In Range("A14", "A" & lastRow)
        loc.Select
        '**WE ARE ON A LOCATION, GO DOWN UNTIL WE REACH THE ROW BEFORE THE NEXT LOCATION**'
            rowNum = WorksheetFunction.Match(loc, Worksheets("SR Data").Range("SRLoc"), 0) + 8
            loc.Offset(1).Select
            While ActiveCell.Value = ""
                ActiveCell.Offset(1).Select
            Wend
            ActiveCell.Offset(-1).Select
            ''INSERT ROW IF NECESSARY**'
            If ActiveCell.Offset(0, 6).Value <> "" Then
                ActiveCell.Offset(1).EntireRow.Insert
                ActiveCell.Offset(1).Select
            End If
            '**ACCOUNT SAVINGS AND FORMATTING**'
            Range("Q" & ActiveCell.Row).Select
            ActiveCell.Value = "Account Savings:"
            ActiveCell.Font.Bold = True
            ActiveCell.HorizontalAlignment = xlRight
            ActiveCell.Offset(0, 2).Select
            ActiveCell.Value = Format(Worksheets("SR Data").Range("D" & rowNum).Value, "Currency")
            ActiveCell.Borders(xlEdgeTop).Weight = xlThin
            '**RUNNING TOTAL**'
            total = total + Worksheets("SR Data").Range("D" & rowNum).Value
            Range(loc, ActiveCell).Interior.Color = Col
            Call ColorSwitch
        End If
    Next
    '**INSERT END OF FILE BORDER, NOTES, TOTAL SAVINGS**'
    Range("A" & Ender).Offset(-1).Select
    Range(ActiveCell, "S13").BorderAround _
        Weight:=xlThick, ColorIndex:=xlColorIndexAutomatic
    Range("P" & Ender).Offset(1).Select
    
    '**TOTAL SAVINGS **'
    ActiveCell.Value = "Total Savings"
    ActiveCell.Font.Bold = True
    Range(ActiveCell, ActiveCell.Offset(0, 3)).Borders(xlEdgeBottom).Weight = xlThin
    Range(ActiveCell, ActiveCell.Offset(0, 3)).Borders(xlEdgeTop).Weight = xlThin
    ActiveCell.Offset(0, 3).Value = Format(total, "Currency")
    ActiveCell.Offset(0, 3).Interior.Color = RGB(204, 255, 204)
    

      
End Sub

Private Sub ColorSwitch()
    '**IF WE ARE ALREADY USING GREEN, SWITCH TO WHITE'
    If Col = RGB(218, 227, 190) Then
        Col = RGB(255, 255, 255)
    Else:
    'WE ARE ALREADY USING WHITE, SWITCH TO GREEN'
        Col = RGB(218, 227, 190)
    End If
End Sub
